Phase0: SoilGrids Soil-Map and India OSM For Land Polygons
Summary
This is a preparatory phase to obtain and store soil-type details and land-boundary polygons for the entire nation. The soil-type details of the land becomes part of core cost to build waterway though that land. The land-boundary polygons define no-go boundary to the search process of G2K project -- making sure that the researched waterway results are within the nation's boundary.
Import SoilGrids Soil-Map
Summary:
This step will download soil-type details from https://www.soilgrids.org/ or HWSD2 from https://www.fao.org/land-water/databases-and-software/en/
Using the soil-type in each location, prepare soil-cost
Go to https://www.soilgrids.org/
Select Download Data
Select Type: SoilGrids
Select Layer: Soil Classification | World Reference Base (2006) Soil Groups
Longitude: MIN: 68.0 MAX: 89.0
Lattitude: MIN: 8.0 MAX: 36.0
Click: Download 250m layer
Review cost-definition to soil-type:
Edit: C:\GangaToKaveri\data\soil\SoilGrids_WRB_2006_FAO_Cost_definition.json
NOTE:
wrbSoilCodeValue corresponds to value in india_soilgrids_250m_layer_WRB_SoilGroups.tif -- used by SoilGrids corresponding to wrbSoilGroup
The hardness is a cost multiplier applied to edge traversal: the cost of edge is multiplied by this number
The soil/subsoil that can be the easiest to excavate will get hardness value of one (1) -- so that only the distance and elevation will matter
Higher value will be assigned to hardness -- depending on how hard to excavate
Use soil-and-bedrock-relative-density-or-consistency-hardness-comparison.xlsx for refence to decide hardness
spec_id | code_value | wrb_soil_group | cost_multiplier | description
---------+------------+----------------+-----------------+---------------------------------------------------------------------------------------------
1 | 255 | NODATA | 1 | #FFFFFF
2 | 254 | Anthrosols | 3 | UNDEFINED-Soils with long and intensive agricultural use
3 | 253 | Technosols | 3.5 | UNDEFINED-Soils containing many artefacts
4 | 0 | Acrisols | 3.5 | Soils with a clay-enriched subsoil Low base status, low-activity clay
5 | 1 | Albeluvisols | 2.5 | Soils with a clay-enriched subsoil Albeluvic tonguing
6 | 2 | Alisols | 3 | Soils with a clay-enriched subsoil Low base status, high-activity clay
7 | 3 | Andosols | 4 | Soils set by Fe/Al chemistry Allophanes or Al-humus complexes
8 | 4 | Arenosols | 1 | Relatively young soils or soils with little profile development Sandy soils
9 | 5 | Calcisols | 2 | Accumulation of less soluble salts or non-saline substances Calcium carbonate
10 | 6 | Cambisols | 2 | Relatively young soils or soils with little profile development Moderately developed soils
11 | 7 | Chernozems | 1.5 | Accumulation of organic matter, high base status Typically mollic
12 | 8 | Cryosols | 3 | Soils with limited rooting due to shallow permafrost Ice-affected soils
13 | 9 | Durisols | 5 | Accumulation of less soluble salts or non-saline substances Silica
14 | 10 | Ferralsols | 5 | Soils set by Fe/Al chemistry Dominance of kaolinite and sesquioxides
15 | 11 | Fluvisols | 1 | Soils influenced by water Floodplains, tidal marshes
16 | 12 | Gleysols | 1.5 | Soils influenced by water Groundwater affected soils
17 | 13 | Gypsisols | 2.5 | Accumulation of less soluble salts or non-saline substances Gypsum
18 | 14 | Histosols | 2 | Soils with thick organic layers
19 | 15 | Kastanozems | 3 | Accumulation of organic matter, high base status Transition to drier climate
20 | 16 | Leptosols | 3.5 | Soils with limited rooting due to stoniness shallow extremely gravelly soils
21 | 17 | Lixisols | 2.5 | Soils with a clay-enriched subsoil High base status, low-activity clay
22 | 18 | Luvisols | 2 | Soils with a clay-enriched subsoil High base status, high-activity clay
23 | 19 | Nitisols | 4.5 | Soils set by Fe/Al chemistry Low-activity clay, P fixation, strongly structured
24 | 20 | Phaeozems | 1.5 | Accumulation of organic matter, Transition to more humid climate
25 | 21 | Planosols | 1.5 | Soils with stagnating water Abrupt textural discontinuity
26 | 22 | Plinthosols | 3.5 | Soils set by Fe/Al chemistry Accumulation of Fe under hydromorphic conditions
27 | 23 | Podzols | 3 | Soils set by Fe/Al chemistry Cheluviation and chilluviation
28 | 24 | Regosols | 1 | Soils with no significant profile development
29 | 25 | Solonchaks | 1.5 | Soils influenced by water Salt enrichment upon evaporation
30 | 26 | Solonetz | 2 | Soils influenced by water Alkaline soils
31 | 27 | Stagnosols | 2 | Soils with stagnating water Structural or moderate textural discontinuity
32 | 28 | Umbrisols | 1.5 | Relatively young soils or soils with little profile development With an acidic dark topsoil
33 | 29 | Vertisols | 1.5 | Soils influenced by water Alternating wet-dry conditions, rich in swelling clays spec_id | code_value | wrb_soil_group | cost_multiplier | description
---------+------------+----------------+-----------------+---------------------------------------------------------------------------------------------
1 | 255 | NODATA | 1 | #FFFFFF
2 | 254 | Anthrosols | 3 | UNDEFINED-Soils with long and intensive agricultural use
3 | 253 | Technosols | 3.5 | UNDEFINED-Soils containing many artefacts
4 | 0 | Acrisols | 3.5 | Soils with a clay-enriched subsoil Low base status, low-activity clay
5 | 1 | Albeluvisols | 2.5 | Soils with a clay-enriched subsoil Albeluvic tonguing
6 | 2 | Alisols | 3 | Soils with a clay-enriched subsoil Low base status, high-activity clay
7 | 3 | Andosols | 4 | Soils set by Fe/Al chemistry Allophanes or Al-humus complexes
8 | 4 | Arenosols | 1 | Relatively young soils or soils with little profile development Sandy soils
9 | 5 | Calcisols | 2 | Accumulation of less soluble salts or non-saline substances Calcium carbonate
10 | 6 | Cambisols | 2 | Relatively young soils or soils with little profile development Moderately developed soils
11 | 7 | Chernozems | 1.5 | Accumulation of organic matter, high base status Typically mollic
12 | 8 | Cryosols | 3 | Soils with limited rooting due to shallow permafrost Ice-affected soils
13 | 9 | Durisols | 5 | Accumulation of less soluble salts or non-saline substances Silica
14 | 10 | Ferralsols | 5 | Soils set by Fe/Al chemistry Dominance of kaolinite and sesquioxides
15 | 11 | Fluvisols | 1 | Soils influenced by water Floodplains, tidal marshes
16 | 12 | Gleysols | 1.5 | Soils influenced by water Groundwater affected soils
17 | 13 | Gypsisols | 2.5 | Accumulation of less soluble salts or non-saline substances Gypsum
18 | 14 | Histosols | 2 | Soils with thick organic layers
19 | 15 | Kastanozems | 3 | Accumulation of organic matter, high base status Transition to drier climate
20 | 16 | Leptosols | 3.5 | Soils with limited rooting due to stoniness shallow extremely gravelly soils
21 | 17 | Lixisols | 2.5 | Soils with a clay-enriched subsoil High base status, low-activity clay
22 | 18 | Luvisols | 2 | Soils with a clay-enriched subsoil High base status, high-activity clay
23 | 19 | Nitisols | 4.5 | Soils set by Fe/Al chemistry Low-activity clay, P fixation, strongly structured
24 | 20 | Phaeozems | 1.5 | Accumulation of organic matter, Transition to more humid climate
25 | 21 | Planosols | 1.5 | Soils with stagnating water Abrupt textural discontinuity
26 | 22 | Plinthosols | 3.5 | Soils set by Fe/Al chemistry Accumulation of Fe under hydromorphic conditions
27 | 23 | Podzols | 3 | Soils set by Fe/Al chemistry Cheluviation and chilluviation
28 | 24 | Regosols | 1 | Soils with no significant profile development
29 | 25 | Solonchaks | 1.5 | Soils influenced by water Salt enrichment upon evaporation
30 | 26 | Solonetz | 2 | Soils influenced by water Alkaline soils
31 | 27 | Stagnosols | 2 | Soils with stagnating water Structural or moderate textural discontinuity
32 | 28 | Umbrisols | 1.5 | Relatively young soils or soils with little profile development With an acidic dark topsoil
33 | 29 | Vertisols | 1.5 | Soils influenced by water Alternating wet-dry conditions, rich in swelling clays Load binary file to soilgrids-group-map table:
Using G2K process pipeline, load the soil-grid data from binary file -- mapped with cost_multiplier, into the database.
Result is a soilgrid soil-group map of India:
Ref: india_soilgrids_250m_layer_WRB_SoilGroups_sample.png
Import India Land Polygons
Osm2psql: Importing PBF file content to PostGreSQL
https://www.cybertec-postgresql.com/en/open-street-map-to-postgis-the-basics/
Visualize and Style: https://www.cybertec-postgresql.com/en/visualizing-osm-data-in-qgis/
OSM-Looking style: https://github.com/yannos/Beautiful_OSM_in_QGIS
Download india.osm.pbf from: https://download.openstreetmap.fr/extracts/asia/india.osm.pbf
Using osm2pgsql to import the land-polygons:
/usr/local/bin/osm2pgsql --style /usr/local/share/osm2pgsql/default.style --username db_app_user --password --database indiaosm --host 172.23.4.30 --port 16432 --number-processes 24 --cache 20480 --create /opt/gistools/pbf/india.osm.pbfEnter password:
<<Will run for over 10min>>
\c indiaosm
Convert web-supporting 3857 to analytics-supporting 4326 (WGS84/WGS 84):
SELECT UpdateGeometrySRID( 'planet_osm_point' , 'way' , 4326 );
SELECT UpdateGeometrySRID( 'planet_osm_line' , 'way' , 4326 );
<<Will run for over 5min>>
SELECT UpdateGeometrySRID( 'planet_osm_polygon' , 'way' , 4326 );
<<Will run for over 5min>>
SELECT UpdateGeometrySRID( 'planet_osm_roads' , 'way' , 4326 );
UPDATE planet_osm_point SET way = ST_TRANSFORM( ST_SETSRID( way, 3857), 4326 );
<<Will run for over 2min>>
UPDATE planet_osm_line SET way = ST_TRANSFORM( ST_SETSRID( way, 3857), 4326 );
<<Will run for over 5min>>
UPDATE planet_osm_polygon SET way = ST_TRANSFORM( ST_SETSRID( way, 3857), 4326 );
<<Will run for over 5min>>
UPDATE planet_osm_roads SET way = ST_TRANSFORM( ST_SETSRID( way, 3857), 4326 );
<<Will run for over 5min>>
select amenity, count(amenity) as amenityCount from planet_osm_point group by amenity order by amenityCount desc;
<<Returns:
amenity | amenitycount
----------------------------------------------+--------------
hospital | 48566
place_of_worship | 27295
clinic | 21455
restaurant | 18255
bank | 14040
school | 12675
>>
SELECT osm_id,name,ref,z_order,way_area,wetland,waterway,ST_Length(way) AS length FROM planet_osm_line
WHERE waterway='river' AND name IN ('Ganges','Ganga Nadi','Ganges Nadi','Ganga')
ORDER BY length DESC;
osm_id | name | ref | z_order | way_area | wetland | waterway | length ------------+------------+-----+---------+----------+---------+----------+---------------------- 197526030 | Ganges | | 0 | | | river | 0.8730690525903018 206113622 | Ganga | | 0 | | | river | 0.866577026261738 82300618 | Ganga | | 0 | | | river | 0.8646836729284196 197526030 | Ganges | | 0 | | | river | 0.8636602200276612 197594615 | Ganga | | 0 | | | river | 0.8622518348651003 166334629 | Ganga | | 0 | | | river | 0.8574754520994917 82295304 | Ganga | | 0 | | | river | 0.8571331850135689 82300618 | Ganga | | 0 | | | river | 0.8568625942055206 82296648 | Ganga | | 0 | | | river | 0.8510075739465573 82296648 | Ganga | | 0 | | | river | 0.8401064834610886
osm_id | name | ref | z_order | way_area | wetland | waterway | length
------------+------------+-----+---------+----------+---------+----------+----------------------
197526030 | Ganges | | 0 | | | river | 0.8730690525903018
206113622 | Ganga | | 0 | | | river | 0.866577026261738
82300618 | Ganga | | 0 | | | river | 0.8646836729284196
197526030 | Ganges | | 0 | | | river | 0.8636602200276612
197594615 | Ganga | | 0 | | | river | 0.8622518348651003
166334629 | Ganga | | 0 | | | river | 0.8574754520994917
82295304 | Ganga | | 0 | | | river | 0.8571331850135689
82300618 | Ganga | | 0 | | | river | 0.8568625942055206
82296648 | Ganga | | 0 | | | river | 0.8510075739465573
82296648 | Ganga | | 0 | | | river | 0.8401064834610886select osm_id,name,ref,z_order,way_area, waterway from planet_osm_line where waterway='river' and name IN ('Ganges','Ganga Nadi','Ganges Nadi','Ganga');
osm_id | name | ref | z_order | way_area | waterway ------------+------------+-----+---------+----------+---------- 44733068 | Ganges | | 0 | | river 807330677 | Ganga Nadi | | 0 | | river 27287354 | Ganga | | 0 | | river 27282950 | Ganga | | 0 | | river
osm_id | name | ref | z_order | way_area | waterway
------------+------------+-----+---------+----------+----------
44733068 | Ganges | | 0 | | river
807330677 | Ganga Nadi | | 0 | | river
27287354 | Ganga | | 0 | | river
27282950 | Ganga | | 0 | | rivershp2pgsql: Importing land boundaries to G2K PostGreSQL
Pre-requisite: populate g2k_graph_scope_specs table
Download split land-polygons (land-polygons-split-4326.zip) from: https://osmdata.openstreetmap.de/data/land-polygons.html
Copy land_polygons.shp, land_polygons.shx, land_polygons.dbf file to: /opt/docker_fs/ganga_to_kaveri/india-osm-db/db_backup/land-polygons-split-4326
cd /opt/db_backup/land-polygons-split-4326
shp2pgsql -I -s 4326 -d land_polygons land_polygons | psql -U db_app_user -d ${POSTGRES_DATABASE}
psql -d ${POSTGRES_DATABASE}
SELECT ST_AsText(geom) FROM land_polygons WHERE x=75 AND y=11;
<<Will show land boundary in Kerala>>
SELECT ST_AsText(geom) FROM land_polygons WHERE x=75 AND y=17;
<<Will show 'box' boundary around the land-mass>>
MULTIPOLYGON(((76.00052426456257 18.0005,76.00052426456257 16.9995,74.99947573543743 16.9995,74.99947573543743 18.0005,76.00052426456257 18.0005)))